package PhotoController;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

import Connect.connector;

public class PhotoYoungDAO{
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public ArrayList<PhotoYoungBean> pt_young_list_main(){
		conn = connector.getConnection();
		ArrayList<PhotoYoungBean> datas=new ArrayList<PhotoYoungBean>();
		try{

			String sql;	
			
			sql = "select * from (select * from BABYPHOTO order by id desc)a where a.id limit 0,4";

			pstmt = conn.prepareStatement(sql);
			
			rs=pstmt.executeQuery();
			while(rs.next()){
				PhotoYoungBean photoYoungBean=new PhotoYoungBean();
				photoYoungBean.setID(rs.getInt("id"));
				photoYoungBean.setBbp_photo(rs.getString("bbp_Photo"));
				datas.add(photoYoungBean);
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas;
	}
	
	public ArrayList<PhotoYoungBean> pt_young_list(HashMap param){
		conn = connector.getConnection();
		ArrayList<PhotoYoungBean> datas=new ArrayList<PhotoYoungBean>();
		try{
			
			String sql;
			sql="select b.* , p.* from BABYPHOTO b,BABYPLUS p where p.id=b.babyplus_id limit ?,?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, (Integer)param.get("startNum"));
			pstmt.setInt(2, (Integer)param.get("pageSize"));
			rs=pstmt.executeQuery();
			while(rs.next()){
				PhotoYoungBean photoYoungBean=new PhotoYoungBean();
				photoYoungBean.setID(rs.getInt("id"));
				photoYoungBean.setMembers_id(rs.getInt("members_id"));
				photoYoungBean.setBby_add(rs.getString("bby_add"));
				photoYoungBean.setBabyplus_Id(rs.getInt("babyplus_id"));
				photoYoungBean.setBbp_Birth(rs.getDate("bbp_birth"));
				photoYoungBean.setBbp_Gender(rs.getInt("bbp_gender"));
				photoYoungBean.setBbp_NameH(rs.getString("bbp_nameh"));
				photoYoungBean.setBbp_NameK(rs.getString("bbp_namek"));
				photoYoungBean.setBbp_photo(rs.getString("bbp_photo"));

				datas.add(photoYoungBean);
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		
		return datas;
	}
	
	public Integer pyTotalCount(HashMap param){
		conn = connector.getConnection();
		int count=0;
		try{
		
			String sql;	
			
			sql="select count(*) as cnt from BABYPHOTO b, BABYPLUS p where p.id=b.babyplus_id;";
			
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
		    	count=rs.getInt("cnt");
		    	System.out.println(count);
		    }
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return count;
		
	}
	public ArrayList<PYoungCommentBean> pt_young_recom (int id){
		conn = connector.getConnection();
		ArrayList<PYoungCommentBean> datas=new ArrayList<PYoungCommentBean>();
		try{
			
			String sql;	
			
			sql = "select * from BABYCOMMENT where babyplus_id=? order by bc_date";
			String sql2;	
			
			sql2 = "select count(*) cnt from BABYCOMMENT where babyplus_id=? order by bc_date";
			
			pstmt = conn.prepareStatement(sql2);
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			if(rs.next()){
				System.out.println("count"+rs.getInt("cnt"));
			}
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();

			
			while(rs.next()){
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				PYoungCommentBean pYoungCommentBean=new PYoungCommentBean();
				pYoungCommentBean.setId(rs.getInt("id"));
				pYoungCommentBean.setBabyplus_id(rs.getInt("babyplus_id"));
				pYoungCommentBean.setBc_nick(rs.getString("bc_nick"));
				pYoungCommentBean.setBc_contents(rs.getString("bc_contents"));
				pYoungCommentBean.setBc_date(sdf.format(rs.getTimestamp("bc_date")));
				datas.add(pYoungCommentBean);
			}
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas;
	}
	
	public void pt_young_recom_write(int id,String bc_contents,String bc_nick){
		conn = connector.getConnection();
		
		try{
			
			String sql;	
			String sql2;
			sql = "insert into BABYCOMMENT (id,babyplus_id,bc_nick,bc_contents,bc_date) values(null,?,?,?,Sysdate())";

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setString(2, bc_nick);
			pstmt.setString(3, bc_contents);
			pstmt.executeUpdate();
			
			
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public void pt_young_com_cancle(int com_id,int babyplus_id){
		conn = connector.getConnection();
		
		try{
			
			String sql;	
			String sql2;
			sql = "delete from BABYCOMMENT where id=?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, com_id);
			pstmt.executeUpdate();
			
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public Integer young_level(String mem_nick){
		conn = connector.getConnection();
		int level=0;
		try{
			
			String sql;	
			sql = "select mem_level from MEMBERS where mem_nick=?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mem_nick);
			rs=pstmt.executeQuery();
			if(rs.next()){
				level=rs.getInt("mem_level");
			}
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return level;
	}
}
